<?php 
class product_plugin_latest extends abstract_product_plugin {

    public function getLatestProducts($limit) { 
        $query = $this->pdo->prepare("SELECT *,
            pd.name AS name, p.image, m.name AS manufacturer, 
            (SELECT price FROM " . $this->db_prefix . "product_discount pd2 
            WHERE pd2.product_id = p.product_id 
            AND pd2.quantity = '1' 
            AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) 
                AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) 
            ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) 
            AS discount, 

            (SELECT price FROM " . $this->db_prefix . "product_special ps 
            WHERE ps.product_id = p.product_id 
            AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) 
            AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) 
            ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) 
            AS special, 

            (SELECT points FROM " . $this->db_prefix . "product_reward pr 
            WHERE pr.product_id = p.product_id) 
            AS reward, 

            (SELECT ss.name FROM " . $this->db_prefix . "stock_status ss 
            WHERE ss.stock_status_id = p.stock_status_id 
            AND ss.language_id = :language_id) 
            AS stock_status, 

            (SELECT wcd.unit FROM " . $this->db_prefix . "weight_class_description wcd 
            WHERE p.weight_class_id = wcd.weight_class_id 
            AND wcd.language_id = :language_id) 
            AS weight_class, 

            (SELECT lcd.unit FROM " . $this->db_prefix . "length_class_description lcd 
            WHERE p.length_class_id = lcd.length_class_id 
            AND lcd.language_id = :language_id) 
            AS length_class, 

            (SELECT AVG(rating) AS total FROM " . $this->db_prefix . "review r1 
            WHERE r1.product_id = p.product_id 
            AND r1.status = '1' 
            GROUP BY r1.product_id) 
            AS rating, 
    
            (SELECT COUNT(*) AS total FROM " . $this->db_prefix . "review r2 
            WHERE r2.product_id = p.product_id 
            AND r2.status = '1' 
            GROUP BY r2.product_id) 
            AS reviews, 

            p.sort_order FROM " . $this->db_prefix . "product p 
            LEFT JOIN " . $this->db_prefix . "product_description pd 
            ON (p.product_id = pd.product_id) 
            LEFT JOIN " . $this->db_prefix . "product_to_store p2s 
            ON (p.product_id = p2s.product_id) 
            LEFT JOIN " . $this->db_prefix . "manufacturer m 
            ON (p.manufacturer_id = m.manufacturer_id) 
            WHERE p.status = '1' 
            AND p.date_available <= NOW() 
            AND p2s.store_id = :store_id 
            ORDER BY p.date_added DESC LIMIT :limit");

        $limit = (int) $limit; 
        $query->bindParam(':limit', $limit, PDO::PARAM_INT);
        $query->bindParam(':language_id', $this->language_id, PDO::PARAM_INT);
        $query->bindParam(':store_id', $this->site_id, PDO::PARAM_INT);
        $query->execute();

        $product_data = array();

        while ($product = $query->fetchObject('class_product')) {
            $product_data[$product->get_id()] = $product;	
        }

        return $product_data;

    }
}
